package dao

import (
	"context"
	"time"

	"gorm.io/gorm"
	"user_srv/basic/config"
	"user_srv/handler/models"
)

// GetFinancialStats 获取财务统计数据
func GetFinancialStats(ctx context.Context, startDate, endDate time.Time, groupType int) ([]*models.FinancialStats, error) {
	var stats []*models.FinancialStats
	startDateStr := startDate.Format("2006-01-02")
	endDateStr := endDate.Format("2006-01-02")
	
	query := config.DB.Model(&models.FinancialStats{}).
		Where("date >= ? AND date <= ?", startDateStr, endDateStr)

	// 根据分组类型进行不同的处理
	var groupExpr string
	switch groupType {
	case 1: // 按天
		groupExpr = "date"
	case 2: // 按月
		groupExpr = "DATE_FORMAT(date, '%Y-%m')"
	case 3: // 按年
		groupExpr = "DATE_FORMAT(date, '%Y')"
	default:
		return nil, gorm.ErrInvalidData
	}

	err := query.
		Group(groupExpr).
		Select(groupExpr+" as date, SUM(income) as income, SUM(expense) as expense, SUM(transaction_count) as transaction_count").
		Find(&stats).Error
	
	// 计算净利润
	for _, stat := range stats {
		stat.NetIncome = stat.Income - stat.Expense
	}
	
	return stats, err
}

// GetFinancialSummary 获取财务汇总信息
func GetFinancialSummary(ctx context.Context, startDate, endDate time.Time) (*models.FinancialSummary, error) {
	var summary models.FinancialSummary
	startDateStr := startDate.Format("2006-01-02")
	endDateStr := endDate.Format("2006-01-02")
	
	baseQuery := config.DB.Model(&models.FinancialStats{}).
		Where("date >= ? AND date <= ?", startDateStr, endDateStr)

	// 使用一次查询获取所有统计数据
	type StatsResult struct {
		TotalIncome  float64 `gorm:"column:total_income"`
		TotalExpense float64 `gorm:"column:total_expense"`
		TotalCount   int64   `gorm:"column:total_count"`
	}
	
	var result StatsResult
	if err := baseQuery.
		Select("COALESCE(SUM(income), 0) as total_income, COALESCE(SUM(expense), 0) as total_expense, COALESCE(SUM(transaction_count), 0) as total_count").
		Scan(&result).Error; err != nil {
		return nil, err
	}

	summary.TotalIncome = result.TotalIncome
	summary.TotalExpense = result.TotalExpense
	summary.TotalTransactions = int(result.TotalCount)
	summary.NetIncome = summary.TotalIncome - summary.TotalExpense

	// 计算收入增长率（与上月同期相比）
	lastMonthStart := startDate.AddDate(0, -1, 0).Format("2006-01-02")
	lastMonthEnd := endDate.AddDate(0, -1, 0).Format("2006-01-02")

	type LastMonthResult struct {
		TotalIncome  float64 `gorm:"column:total_income"`
		TotalExpense float64 `gorm:"column:total_expense"`
	}
	
	var lastMonthResult LastMonthResult
	if err := config.DB.Model(&models.FinancialStats{}).
		Where("date >= ? AND date <= ?", lastMonthStart, lastMonthEnd).
		Select("COALESCE(SUM(income), 0) as total_income, COALESCE(SUM(expense), 0) as total_expense").
		Scan(&lastMonthResult).Error; err == nil {
		if lastMonthResult.TotalIncome > 0 {
			summary.IncomeChangeRate = (summary.TotalIncome - lastMonthResult.TotalIncome) / lastMonthResult.TotalIncome * 100
		}
		if lastMonthResult.TotalExpense > 0 {
			summary.ExpenseChangeRate = (summary.TotalExpense - lastMonthResult.TotalExpense) / lastMonthResult.TotalExpense * 100
		}
		lastNetIncome := lastMonthResult.TotalIncome - lastMonthResult.TotalExpense
		if lastNetIncome != 0 {
			summary.NetChangeRate = (summary.NetIncome - lastNetIncome) / lastNetIncome * 100
		}
	}

	return &summary, nil
}

// GetIncomeBreakdown 获取收入明细
func GetIncomeBreakdown(ctx context.Context, startDate, endDate time.Time, page, pageSize int) ([]*models.IncomeBreakdownItem, int64, error) {
	var items []*models.IncomeBreakdownItem
	var total int64

	query := config.DB.Model(&models.IncomeBreakdownItem{}).
		Where("date_range >= ? AND date_range <= ?", startDate.Format("2006-01-02"), endDate.Format("2006-01-02"))

	// 计算总数（使用克隆查询避免影响主查询）
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	// 分页查询
	offset := (page - 1) * pageSize
	err := query.
		Order("amount DESC").
		Offset(offset).
		Limit(pageSize).
		Find(&items).Error

	return items, total, err
}

// GetExpenseBreakdown 获取支出明细
func GetExpenseBreakdown(ctx context.Context, startDate, endDate time.Time, page, pageSize int) ([]*models.ExpenseBreakdownItem, int64, error) {
	var items []*models.ExpenseBreakdownItem
	var total int64

	query := config.DB.Model(&models.ExpenseBreakdownItem{}).
		Where("date_range >= ? AND date_range <= ?", startDate.Format("2006-01-02"), endDate.Format("2006-01-02"))

	// 计算总数（使用克隆查询避免影响主查询）
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	// 分页查询
	offset := (page - 1) * pageSize
	err := query.
		Order("amount DESC").
		Offset(offset).
		Limit(pageSize).
		Find(&items).Error

	return items, total, err
}

// GetTransactionList 获取交易明细列表
func GetTransactionList(ctx context.Context, startDate, endDate time.Time, transactionType string, page, pageSize int) ([]*models.TransactionItem, int64, error) {
	var items []*models.TransactionItem
	var total int64

	query := config.DB.Model(&models.TransactionItem{}).
		Where("transaction_time >= ? AND transaction_time <= ?", startDate.Format("2006-01-02"), endDate.Format("2006-01-02"))

	// 添加交易类型筛选
	if transactionType != "" && transactionType != "all" {
		query = query.Where("transaction_type = ?", transactionType)
	}

	// 计算总数（使用克隆查询避免影响主查询）
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	// 分页查询
	offset := (page - 1) * pageSize
	err := query.
		Order("transaction_time DESC").
		Offset(offset).
		Limit(pageSize).
		Find(&items).Error

	return items, total, err
}

// CreateTransaction 创建交易记录
func CreateTransaction(ctx context.Context, tx *models.TransactionItem) error {
	return config.DB.Create(tx).Error
}

// UpdateFinancialStats 更新财务统计数据
func UpdateFinancialStats(ctx context.Context, stats *models.FinancialStats) error {
	return config.DB.Save(stats).Error
}

// CreateFinancialStats 创建财务统计数据
func CreateFinancialStats(ctx context.Context, stats *models.FinancialStats) error {
	return config.DB.Create(stats).Error
}
